package br.com.ffm.praticas.connection;

import java.sql.*;

import br.com.ffm.praticas.constants.Constant;

public class ConnectionDB {
	
	private static ConnectionDB connectionDB;
	private static Connection conn = null;

	public static ConnectionDB getInstance(){
		if (connectionDB == null)
			connectionDB = new ConnectionDB();
		return connectionDB;
	}
	
	/**
	 * Method that provides the Connection with MySql Data Base
	 * @return Connection
	 * @throws SQLException
	 */
	public Connection getConnection() throws SQLException{
		try {
			Class.forName(Constant.DRIVER_NAME);
			conn = DriverManager.getConnection(Constant.MYSQL_CONNECTION_PATH);
			return conn;
		} catch (SQLException sqlExcep) {
			sqlExcep.printStackTrace();
		}catch (ClassNotFoundException cnfExcep) {
			cnfExcep.printStackTrace();
		}
		return null;
	}
	
	/**
	 * Method that close the Connection with the MySql Data Base
	 * @throws SQLException
	 */
	public void close() throws SQLException {
		if (conn != null) {
			conn.close();
		}
	}
	
	/**
	 * Method that executes a SQL Reader Command on MySql Data Base
	 * @param query Query
	 * @return ResultSet
	 * @throws SQLException
	 */
	public ResultSet executeReader(String query) throws SQLException {
		getConnection();
		Statement stm = conn.createStatement();
		stm.setPoolable(true);
		ResultSet result = stm.executeQuery(query);
		return result;
	}
	
	/**
	 * Method that executes a SQL Scalar
	 * @param query Query 
	 * @param scalarCommandType Scalar Command Type
	 * @param field Table Field
	 * @return return the query scalar command result
	 * @throws SQLException
	 */
	public int executeScalar(String query, String scalarCommandType, String field) throws SQLException{
		getConnection();
		Statement stm = conn.createStatement();
		int result = -1;
		try {
			ResultSet rs = stm.executeQuery(query);
			if (rs.next())
				result = rs.getInt(scalarCommandType + "(" + field + ")");
			close();
			return result;
		} catch (SQLException sqlExcp) {
			sqlExcp.printStackTrace();
		} finally {
			close();
		}
		return result;
	}
	
	/**
	 * Method that executes a SQL Command on MySql Data Base
	 * @param query Query
	 * @throws SQLException
	 */
	public void executeCommand(String query) throws SQLException{
		getConnection();
		try {
			PreparedStatement pstm = conn.prepareStatement(query);
			pstm.setPoolable(true);
			pstm.execute(query);
		} catch (SQLException sqlExcp) {
			System.out.println(sqlExcp.getMessage());
		} finally {
			close();
		}
		
	}

}
